package lab03.test13;
import java.sql.*;
public class Test13 {
    public static void main(String[] args) {

        try{
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        Connection connection=null;
Statement statement=null;
ResultSet resultSet=null;
try {
    connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/java_2017?serverTimezone=UTC&characterEncoding=gbk","root","w19971201t");
    statement=connection.createStatement();
    statement.execute("create table  t_students(id bigint(20),stu_no varchar(50)PRIMARY KEY,real_name varchar(50) ,age tinyint(4),gender tinyint(4),department varchar(50))");
//建立t_students表
    statement.executeUpdate("INSERT INTO t_students(id,stu_no,real_name,age,gender,department) VALUES(1,'1606101065','汪涛',20,0,'信息与工程学院') ");
    statement.executeUpdate("INSERT INTO t_students(id,stu_no,real_name,age,gender,department) VALUES(2,'1606101066','肖林',20,0,'信息与工程学院') ");
    statement.executeUpdate("INSERT INTO t_students(id,stu_no,real_name,age,gender,department) VALUES(3,'1606101067','易佳',20,0,'旅游文化产业学院') ");
    statement.executeUpdate("INSERT INTO t_students(id,stu_no,real_name,age,gender,department) VALUES(4,'1606101068','朱晓东',20,0,'信息与工程学院') ");
    statement.executeUpdate("INSERT INTO t_students(id,stu_no,real_name,age,gender,department) VALUES(5,'1606101069','冷婷婷',20,1,'信息与工程学院') ");

    resultSet=statement.executeQuery("SELECT * FROM t_students ");
    while (resultSet.next()){
        System.out.print(resultSet.getString(1)+" ");
        //getString为列号
        System.out.print(resultSet.getString(2)+" ");
        System.out.print(resultSet.getString(3)+" ");
        System.out.print(resultSet.getString(4)+" ");
        System.out.print(resultSet.getString(5)+" ");
        System.out.print(resultSet.getString(6));
        System.out.println();
    }
   statement.executeUpdate("DELETE FROM t_students WHERE id=5");
    statement.executeUpdate("UPDATE t_students SET department ='信息与工程学院'WHERE id=3 ");
} catch (SQLException e) {
    e.printStackTrace();
}
finally {
    try {
        connection.close();
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

}

    }
}
